/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package tuitionmanagement.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import tuitionmanagement.entity.Invoice;
import tuitionmanagement.util.DBUtil;

/**
 *
 * @author XUAN
 */
public class InvoiceDao {

    private Connection con;
    private ResultSet rs;
    private PreparedStatement pst;
  
    private String lastError;
    //private final String sql_SelectReadByAll = "select InvoiceId, StudentName,ClassTypeName,FeeLevel.FeeLevelId from Invoice join Student on Invoice.StudentId = Student.StudentId join ClassType on ClassType.ClassTypeId = Invoice.ClassTypeId join FeeLevel on FeeLevel.FeeLevelId = Invoice.FeeLevelId";
    final String sql_SelectReadByAll = "select InvoiceId,Student.StudentID, StudentName,ClassType.ClassTypeID,ClassTypeName,FeeLevel.FeeLevelId from Invoice join Student on Invoice.StudentId = Student.StudentId join ClassType on ClassType.ClassTypeId = Invoice.ClassTypeId join FeeLevel on FeeLevel.FeeLevelId = Invoice.FeeLevelId";
    private final String SQL_INSERT = "insert into Invoice(StudentID, ClassTypeID, FeeLevelID) values(?,?,?)";
    private final String SQL_UPDATE = "update Invoice set StudentID=?, ClassTypeID=?, FeeLevelID=?";
    private final String SQL_DELETE = "delete from Invoice where InvoiceID=?";

    public ArrayList<Invoice> readByAll() {
        ArrayList<Invoice> list = new ArrayList<Invoice>();
        con = DBUtil.getConn();
        try {
            pst = con.prepareStatement(sql_SelectReadByAll);
            rs = pst.executeQuery();
            while (rs.next()) {
                Invoice f = new Invoice();
                f.setId(rs.getInt("InvoiceId"));
                f.getStudent().setStudentId(rs.getInt("StudentID"));
                f.getStudent().setStudentName(rs.getString("StudentName"));
                f.getClassType().setId(rs.getInt("ClassTypeID"));
                f.getClassType().setName(rs.getString("ClassTypeName"));
                f.getFeeLevel().setFeeLevelId(rs.getInt("FeeLevelId"));

                list.add(f);
            }
        } catch (SQLException ex) {
            setLastError("SQL Error!");
        } finally {
            DBUtil.closeConn(con);
        }
        return list;
    }

    public boolean insert(Invoice invoice) {
        int eff = 0;
        try {
            con = DBUtil.getConn();
            pst = con.prepareStatement(SQL_INSERT);
            pst.setInt(1, invoice.getStudent().getStudentId());
            pst.setInt(2, invoice.getClassType().getId());
            pst.setInt(3, invoice.getFeeLevel().getFeeLevelId());
            eff = pst.executeUpdate();
            pst.close();
            if (eff > 0) {
                return true;
            }
            
        } catch (SQLException ex) {
           lastError=ex.getMessage();
        }
        catch(Exception ex){lastError=ex.getMessage();}
        finally{
            DBUtil.closeConn(con);
        }       
        return false;
    }
    public boolean  delete(Invoice invoice){
         int eff = 0;
        try {
            con = DBUtil.getConn();
            pst = con.prepareStatement(SQL_DELETE);
            pst.setInt(1, invoice.getId());         
            eff = pst.executeUpdate();
            pst.close();
            if (eff > 0) {
                return true;
            }

        } catch (SQLException ex) {
           lastError=ex.getMessage();
        }
        catch(Exception ex){lastError=ex.getMessage();}
        finally{
            DBUtil.closeConn(con);
        }
        return false;
    }


    /**
     * @return the lastError
     */
    public String getLastError() {
        return lastError;
    }

    /**
     * @param lastError the lastError to set
     */
    public void setLastError(String lastError) {
        this.lastError = lastError;
    }




}
